package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import com.zretc.dao.SellerDataDao;
import com.zretc.util.DBUtil;

import javafx.scene.chart.PieChart.Data;

/**
 * @author wentao
 * 2020年6月20日18:23:41
 * 商家首页显示的dao层实现类
 */
public class SellerDataDaoImpl implements SellerDataDao{

	@Override
	public int newOrderNumber(Integer sellerId) {
		String sql = "select count(*) from (select count(*) from order_detail where product_id in (select product_id from product where seller_id = ?) and order_id in (select order_id from orders where order_time between ? and ?) group by order_id) as temp";
		// 获取当前时间
		Date newDate = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String date = format.format(newDate);
		// 获取一个月前时间
		Calendar c = Calendar.getInstance();
		c.setTime(new Date());
        c.add(Calendar.MONTH, -1);
        newDate = c.getTime();
		String beforDate = format.format(newDate);
		
		ResultSet rs = DBUtil.doQuery(sql, sellerId,beforDate,date);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int shippedOrderNumber(Integer sellerId) {
		String sql = "select count(*) from (select count(*) from order_detail where product_id in (select product_id from product where seller_id = ?) and order_id in (select order_id from orders where order_status = 2) group by order_id) as temp";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int finishOrderNumber(Integer sellerId) {
		String sql = "select count(*) from (select count(*) from order_detail where product_id in (select product_id from product where seller_id = ?) and order_id in (select order_id from orders where order_status = 3) group by order_id) as temp";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int allOrderNumber(Integer sellerId) {
		String sql = "select count(*) from (select count(*) from order_detail where product_id in (select product_id from product where seller_id = ?) group by order_id) as temp";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int waitOrderNumber(Integer sellerId) {
		String sql = "select count(*) from (select count(*) from order_detail where product_id in (select product_id from product where seller_id = ?) and order_id in (select order_id from orders where order_status = 1) group by order_id) as temp";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int collectShopUserNumber(Integer sellerId) {
		String sql = "select count(*) from shops_collect where seller_id = ?";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int shoppingProductUserNumber(Integer sellerId) {
		String sql = "select count(*) from orders where order_id in (select order_id from order_detail where product_id in (select product_id from product where seller_id = ?))";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int collectProductUserNumber(Integer sellerId) {
		String sql = "select count(*) from (select count(*) from product_collect where product_id in (select product_id from product where seller_id = ?) group by user_id) as temp";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public String lastOrderDate(Integer sellerId){
		String sql = "select order_time from orders where order_id in (select order_id from order_detail where product_id in (select product_id from product where seller_id = ?)) order by order_time desc limit 0,1";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		String date = "";
		try {
			while(rs.next()) {
				date = rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 格式化时间
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		Date parse = null;
		if(!"".equals(date)) {
			try {
				parse = format.parse(date);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			date = format.format(parse);
		}
		
		return date;
	}

	@Override
	public int allCommentNumber(Integer sellerId) {
		String sql = "select count(*) from `comment` where product_id in (select product_id from product where seller_id =?)";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int replyCommentNumber(Integer sellerId) {
		String sql = "select count(*) from `comment` where product_id in (select product_id from product where seller_id = ?) and user_id = 0";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer number = 0;
		try {
			while(rs.next()) {
				number = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return number;
	}

	@Override
	public int waitReplyCommentNumber(Integer sellerId) {
		// 已回复的一级评论
		String sql = "select count(*) from (select comment_father_id from `comment` where user_id = 0 and product_id in (select product_id from product where seller_id = ?) group by comment_father_id) as temp";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Integer replyNumber = 0;
		try {
			while(rs.next()) {
				replyNumber = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 全部一级评论
		sql = "select count(*) from `comment` where product_id in (select product_id from product where seller_id = ?) and comment_father_id = 0";
		rs = DBUtil.doQuery(sql, sellerId);
		Integer allNumber = 0;
		try {
			while(rs.next()) {
				allNumber = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return allNumber-replyNumber;
	}

	@Override
	public Map<String,Float> monthSales(Integer sellerId) {
		String sql = "select DATE_FORMAT(sales_date,\"%Y-%m-%d\") as date,sum(sales_price) as price from sales where product_id in (select product_id from product where seller_id = ?) and date_format(sales_date,'%Y-%m') = ? group by date_format(sales_date,'%Y-%m-%d')";
		// 要查询的月份（当前月份）
		Date newDate = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
		String date = format.format(newDate);
		ResultSet rs = DBUtil.doQuery(sql, sellerId,date);
		SimpleDateFormat f = new SimpleDateFormat("dd");
		SimpleDateFormat all = new SimpleDateFormat("yyyy-MM-dd");
		Map<String,Float> map = new HashMap<String, Float>();
		try {
			while(rs.next()) {
				
				String day = rs.getString(1);
				Float money = rs.getFloat(2);
				// 格式化时间
				Date parse = null;
				try {
					parse = all.parse(day);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				day = f.format(parse);
				map.put(day, money);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}

	@Override
	public Map<String, Integer> placeTheOrderUserNumber(Integer sellerId) {
		StringBuilder sql = new StringBuilder("select DATE_FORMAT(B.order_time,\"%Y-%m-%d\") as date,count(DISTINCT A.order_id) as count from order_detail A") 
				.append(" left join orders B ")
				.append(" on A.order_id = B.order_id")
				.append(" left join product C")
				.append(" on C.product_id = A.product_id\r\n")
				.append(" where C.seller_id = ? and DATE_FORMAT(B.order_time,\"%Y-%m\") = ?")
				.append(" group by  DATE_FORMAT(B.order_time,\"%Y-%m-%d\")");
		// 要查询的月份（当前月份）
		Date newDate = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
		String date = format.format(newDate);
		ResultSet rs = DBUtil.doQuery(sql.toString(), sellerId,date);
		SimpleDateFormat f = new SimpleDateFormat("dd");
		SimpleDateFormat all = new SimpleDateFormat("yyyy-MM-dd");
		Map<String,Integer> map = new HashMap<String, Integer>();
		try {
			while(rs.next()) {
				String day = rs.getString(1);
				Integer count = rs.getInt(2);
				Date parse = all.parse(day);
				day = f.format(parse);
				map.put(day, count);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}

	@Override
	public Map<String, Integer> getProductInventory(Integer sellerId) {
		String sql = "select product_name,product_inventory from product where seller_id = ?";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		Map<String, Integer> map = new HashMap<>();
		try {
			while(rs.next()) {
				String productName = rs.getString("product_name");
				int productInventory = rs.getInt("product_inventory");
				map.put(productName, productInventory);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}

}
